package persistence.dao;

import common.util.DateUtils;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashSet;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

import core.ModelObject;
import persistence.DbUtils;
import persistence.TransactionManager;

import persistence.dto.ItemDTO;

public class ItemsDBDAO extends ModelObject implements IItemsDAO
{

	/**
	 * 
	 */
	private static final long serialVersionUID = -8137784941694913312L;
	private static ItemsDBDAO instance;
	
	public static ItemsDBDAO getInstance()
	{
		if(instance == null)
		{
			instance = new ItemsDBDAO();
		}
		return instance;
	}
	
	private ItemsDBDAO()
	{
		
	}
	
	@Override
	public int add(ItemDTO itemDTO)
	{
            Statement keyStmt = null;
            ResultSet keyRS = null;
            PreparedStatement stmt = null;
            int id = -1; //if id isn't set, -1 is error
            
            if(itemDTO.getId() != 0) 
              return itemDTO.getId();
            
		try
                {
                    String query = "insert into Items (barcode, "
                            + "productID, entryDate, exitTime, "
                            + "expDate, containerID, removed)"
                            + "values (?,?,?,?,?,?,?)";
                    stmt = TransactionManager.getConnection().prepareStatement(query);
                    stmt.setString(1, itemDTO.getBarcode());
                    stmt.setInt(2, itemDTO.getProductID());
                    stmt.setString(3, itemDTO.getEntryDate());
                    stmt.setString(4, itemDTO.getExitTime());
                    stmt.setString(5, itemDTO.getExpirationDate());
                    stmt.setInt(6, itemDTO.getParentContainerID());
                    stmt.setBoolean(7, itemDTO.getRemoved());
                    if(stmt.executeUpdate() == 1)
                    {
                        keyStmt = TransactionManager.getConnection().createStatement();
                        keyRS = keyStmt.executeQuery("select last_insert_rowid()");
                        keyRS.next();
                        id = keyRS.getInt(1); //ID of the new book
                    }
                    if (stmt != null) stmt.close();
                    if (keyRS != null) keyRS.close();
                    if (keyStmt != null) keyStmt.close();
                
                }
		catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		return id;
	}

	@Override
	public boolean edit(ItemDTO itemDTO)
	{
            try{
                String query = "update Items set barcode = ?, productID = ?, "
                        + "entryDate = ?, exitTime = ?, expDate = ?, containerID = ?, "
                        + "removed = ? where id = ?";
                PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
                
                stmt.setString(1, itemDTO.getBarcode());
                stmt.setInt(2, itemDTO.getProductID());
                stmt.setString(3, itemDTO.getEntryDate());
                stmt.setString(4, itemDTO.getExitTime());
                stmt.setString(5, itemDTO.getExpirationDate());
                stmt.setInt(6, itemDTO.getParentContainerID());
                stmt.setBoolean(7, itemDTO.getRemoved());
                stmt.setInt(8, itemDTO.getId());
                if(stmt.executeUpdate() == 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
                
            }
            catch (SQLException e) {
			System.out.println(e.getMessage());
            }
            System.out.println("unable to edit item in Items table");
            
            return false;
	}

	@Override
	public boolean remove(ItemDTO itemDTO)
	{
            try{
                    String query = "delete from Items where id = ?";
                    PreparedStatement stmt = 
                    	TransactionManager.getConnection().prepareStatement(query);
                    stmt.setInt(1, itemDTO.getId());
                    if(stmt.executeUpdate() == 1)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (SQLException e) {
			System.out.println(e.getMessage());
		}
                System.out.println("unable to delete item in Items table");
                
		return false;
	}

	@Override
	public Set<ItemDTO> getAllItems()
	{
            Set<ItemDTO> allItems = new HashSet<ItemDTO>();
            ResultSet rs = null;
            PreparedStatement stmt = null;
            
            try{
                String query = "select * from Items";
                stmt = TransactionManager.getConnection().prepareStatement(query);
                rs = stmt.executeQuery();
                
                while(rs.next())
                {
                    int id = rs.getInt(1);
                    String barcode = rs.getString(2);
                    int productID = rs.getInt(3);
                    Calendar entryDate = new GregorianCalendar();
                    Calendar exitTime = new GregorianCalendar();
                    Calendar expDate = new GregorianCalendar();
                    
                    //parse the date string
                    //
                    String string4, string5, string6;
                    Date d = DateUtils.parseDateTime(rs.getString(4));
                    entryDate.setTime(d);
                    string5 = rs.getString(5);
                    if(string5.isEmpty())
                    	exitTime = null;
                    else
                    	exitTime.setTime(DateUtils.parseDateTime(rs.getString(5)));
                    string6 = rs.getString(6);
                    if(string6.isEmpty())
                    	expDate = null;
                    else
                    	expDate.setTime(DateUtils.parseDateTime(rs.getString(6)));
                    
                    int containerID = rs.getInt(7);
                    boolean removed = rs.getBoolean(8);
                    
                    ItemDTO iDTO = new ItemDTO(id, barcode, 
                            entryDate, exitTime, expDate, productID, 
                            containerID, removed);
                    
                    allItems.add(iDTO);
                }
            }
            catch (SQLException e) 
            {
            	System.out.println(e.getMessage());
            }
            catch(ParseException e)
            {
            	System.out.println(e.getMessage());
            }
            finally {
			DbUtils.safeClose(rs);
			DbUtils.safeClose(stmt);
            }
		
		return allItems;
	}
}
